﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using iShoppingDTO;
using System.Data;
using System.Data.SqlClient;

namespace iShoppingDAO
{
    public class ThongKeDAO
    {
        public static int DoanhThuTheoThangDAO(int _nam, int _thang)
        {
            int gia = 0 ;
            try
            {

                string sql = "select sum(gdtc.GiaKhopLenh) as 'Gia' from GIAODICHTHANHCONG gdtc, CHITIETGIAODICH ctgd where ctgd.MaChiTietGiaoDich = gdtc.MaChiTietGiaoDich and ctgd.MaChiTIetGiaoDIch in( select maChiTIetGIaoDich from CHITIETGIAODICH where year(ThoiGianGiaoDich) = " + _nam.ToString() + " and month(ThoiGianGiaoDich) = " + _thang.ToString() + ")";
                DataTable dt = DataProvider.ExecuteQuery(sql);
                gia = int.Parse(dt.Rows[0]["Gia"].ToString());
            }
            catch (Exception ex)
            {
            }
            return gia;
        }

        //doanh thu theo Quy
        public static int DoanhThuTheoQuyDAO(int _nam, int _quy)
        {
            int gia = 0;
            try
            {

                string sql = "";
                if (_quy == 1)
                {
                    sql = "select sum(gdtc.GiaKhopLenh) as 'DoanhThu' from GIAODICHTHANHCONG gdtc, CHITIETGIAODICH ctgd where ctgd.MaChiTietGiaoDich = gdtc.MaChiTietGiaoDich and ctgd.MaChiTIetGiaoDIch in( select maChiTIetGIaoDich from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 1 or month(ThoiGianGiaoDich) = 2 or month(ThoiGianGiaoDich) = 3) ";
                }
                if (_quy == 2)
                {
                    sql = "select sum(gdtc.GiaKhopLenh) as 'DoanhThu' from GIAODICHTHANHCONG gdtc, CHITIETGIAODICH ctgd where ctgd.MaChiTietGiaoDich = gdtc.MaChiTietGiaoDich and ctgd.MaChiTIetGiaoDIch in( select maChiTIetGIaoDich from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 4 or month(ThoiGianGiaoDich) = 5 or month(ThoiGianGiaoDich) = 6) ";
                }
                if (_quy == 3)
                {
                    sql = "select sum(gdtc.GiaKhopLenh) as 'DoanhThu' from GIAODICHTHANHCONG gdtc, CHITIETGIAODICH ctgd where ctgd.MaChiTietGiaoDich = gdtc.MaChiTietGiaoDich and ctgd.MaChiTIetGiaoDIch in( select maChiTIetGIaoDich from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 7 or month(ThoiGianGiaoDich) = 8 or month(ThoiGianGiaoDich) = 9) ";
                }
                if (_quy == 4)
                {
                    sql = "select sum(gdtc.GiaKhopLenh) as 'DoanhThu' from GIAODICHTHANHCONG gdtc, CHITIETGIAODICH ctgd where ctgd.MaChiTietGiaoDich = gdtc.MaChiTietGiaoDich and ctgd.MaChiTIetGiaoDIch in( select maChiTIetGIaoDich from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 10 or month(ThoiGianGiaoDich) = 11 or month(ThoiGianGiaoDich) = 12) ";
                }
                DataTable dt = DataProvider.ExecuteQuery(sql);
                gia = int.Parse(dt.Rows[0]["Gia"].ToString());
            }
            catch (Exception ex)
            {
            }
            return gia;
        }
        //Doanh thu theo năm
        public static DataTable DoanhThuTheoNamDAO()
        {
            DataTable dt = null;
            try
            {

                string sql = " select year(ThoiGianGiaoDich)as Nam, sum(GiaKhopLenh) as TongDoanhThu from GIAODICHTHANHCONG gdtc, CHITIETGIAODICH ctgd where gdtc.MaChiTietGiaoDich = ctgd.MaChiTietGiaoDich  group by year(ThoiGianGiaoDich)";
                dt = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception ex)
            {
            }
            return dt;
        }

        //số lượng giao dịch theo tháng
        public static int GiaoDichTheoThangDAO(int _nam, int _thang)
        {
            int giaodich = 0;
            try
            {

                string sql = "select count(MaChiTietGiaoDich)as 'SLGiaoDich'from CHITIETGIAODICH where month(ThoiGianGiaoDich) = "+_thang.ToString()+" and year(ThoiGianGiaoDich) ="+_nam.ToString();
                DataTable dt = DataProvider.ExecuteQuery(sql);
                giaodich = int.Parse(dt.Rows[0]["SLGiaoDich"].ToString());
            }
            catch (Exception ex)
            {
            }
            return giaodich;
        }
        //so luong giao dich theo Quy
        public static int GiaoDichTheoQuyDAO(int _nam, int _quy)
        {
            int gia = 0;
            try
            {

                string sql = "";
                if (_quy == 1)
                {
                    sql = "select count(MaChiTietGiaoDich) as 'SLGiaoDich'from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 1 or month(ThoiGianGiaoDich) = 2 or month(ThoiGianGiaoDich) = 3";
                }
                if (_quy == 2)
                {
                    sql = "select count(MaChiTietGiaoDich) as 'SLGiaoDich'from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 4 or month(ThoiGianGiaoDich) = 5 or month(ThoiGianGiaoDich) = 6";
                }
                if (_quy == 3)
                {
                    sql = "select count(MaChiTietGiaoDich) as 'SLGiaoDich'from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 7 or month(ThoiGianGiaoDich) = 8 or month(ThoiGianGiaoDich) = 9";
                }
                if (_quy == 4)
                {
                    sql = "select count(MaChiTietGiaoDich) as 'SLGiaoDich'from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 10 or month(ThoiGianGiaoDich) = 11 or month(ThoiGianGiaoDich) = 12";
                }

                DataTable dt = DataProvider.ExecuteQuery(sql);
                gia = int.Parse(dt.Rows[0]["SLGiaoDich"].ToString());
            }
            catch (Exception ex)
            {
            }
            return gia;
        }
        //số lượng giao dịch theo năm
        public static DataTable GiaoDichTheoNamDAO()
        {
            DataTable dt = null;
            try
            {

                string sql = " select year(ThoiGianGiaoDich) as Nam, count(MaChiTietGiaoDich) as SL_CTGD from CHITIETGIAODICH group by year(ThoiGianGiaoDich)";
                dt = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception ex)
            {
            }
            return dt;
        }
        //số lượng sản phẩm đc giao dịch theo tháng
        public static DataTable SanPhamTheoThangDAO(int _nam)
        {
            DataTable dt = null;
            try
            {

                string sql = "select count(MaChiTietGiaoDich) as SL_CTGD, month(ThoiGianGiaoDich) as Thang from CHITIETGIAODICH where year(ThoiGianGiaoDich) = "+_nam.ToString()+" group by month(ThoiGianGiaoDich)";
             dt = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception ex)
            {
            }
            return dt;
        }

        // số lượng sản pẩm đc giao dịch theo quý
        public static int SanPhamTheoQuyDAO(int _nam, int _quy)
        {
            int gia = 0;
            try
            {

                string sql = "";
                if (_quy == 1)
                {
                    sql = "select count(MaChiTietGiaoDich) as SL_CTGD from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 1 or month(ThoiGianGiaoDich) = 2 or month(ThoiGianGiaoDich) = 3";
                }
                if (_quy == 2)
                {
                    sql = "select count(MaChiTietGiaoDich) as SL_CTGD from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 4 or month(ThoiGianGiaoDich) = 5 or month(ThoiGianGiaoDich) = 6";
                }
                if (_quy == 3)
                {
                    sql = "select count(MaChiTietGiaoDich) as SL_CTGD from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 7 or month(ThoiGianGiaoDich) = 8 or month(ThoiGianGiaoDich) = 9";
                }
                if (_quy == 4)
                {
                    sql = "select count(MaChiTietGiaoDich) as SL_CTGD from CHITIETGIAODICH where year(ThoiGianGiaoDich) = 2011 and month(ThoiGianGiaoDich) = 10 or month(ThoiGianGiaoDich) = 11 or month(ThoiGianGiaoDich) = 12";
                }

                DataTable dt = DataProvider.ExecuteQuery(sql);
                gia = int.Parse(dt.Rows[0]["SL_CTGD"].ToString());
            }
            catch (Exception ex)
            {
            }
            return gia;
        }
        //số lượng sản phẩm được giao dịch theo năm
        public static DataTable SanPhamGiaoDichTheoNamDAO()
        {
            DataTable dt = null;
            try
            {

                string sql = " select year(ThoiGianGiaoDich) as Nam, count(MaChiTietGiaoDich) as SL_CTGD from CHITIETGIAODICH group by year(ThoiGianGiaoDich)";
                dt = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception ex)
            {
            }
            return dt;
        }


    }
}
